require('dotenv').config();
const express = require('express');
const multer = require('multer');
const mysql = require('mysql2/promise');
const cors = require('cors');
const fs = require('fs');
const path = require('path');

const app = express();
const port = process.env.PORT || 3000;

// 中间件
app.use(cors());
app.use(express.json());
app.use('/uploads', express.static('uploads'));

// 数据库连接配置
const dbConfig = {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'image_storage_system',
    charset: 'utf8mb4',  // 关键设置
};


// 文件存储配置
const storage = multer.diskStorage({
    destination: (req, file, cb) => {
        const uploadDir = 'uploads';
        if (!fs.existsSync(uploadDir)) {
            fs.mkdirSync(uploadDir);
        }
        cb(null, uploadDir);
    },
    filename: (req, file, cb) => {
        cb(null, Date.now() + path.extname(file.originalname));
    }
});

const upload = multer({ storage });

// 路由
app.get('/', (req, res) => {
    res.sendFile(path.join(__dirname, 'public', 'index.html'));
});

// 上传接口
app.post('/api/upload', upload.array('images'), async (req, res) => {
    try {
        const { imageType, college } = req.body;
        const files = req.files;
        
        if (!files || files.length === 0) {
            return res.status(400).json({ error: 'No files uploaded' });
        }
        
        const connection = await mysql.createConnection(dbConfig);

        await connection.query("SET NAMES 'utf8mb4'");
        
        const results = [];
        for (const file of files) {
            const originalName = Buffer.from(file.originalname, "latin1").toString("utf8")
            // const originalName = file.originalname;
            const parsedData = parseFileName(originalName);  
            
            console.log(parsedData)
            const [result] = await connection.execute(
                'INSERT INTO images (student_name, student_id, year, major, class_info, college, image_type, file_name, file_path) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
                [
                    parsedData.studentName,
                    parsedData.studentId,
                    parsedData.year,
                    parsedData.major,
                    parsedData.classInfo,
                    college,
                    imageType,
                    originalName,
                    file.path
                ]
            );
            
            results.push({
                id: result.insertId,
                ...parsedData,
                college,
                imageType,
                filePath: file.path
            });
        }
        
        await connection.end();
        
        res.json({ success: true, data: results });
    } catch (error) {
        console.error('Upload error:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
});

/**
 * @api {get} /api/images/search 多条件查询图片
 * @apiName SearchImages
 * @apiGroup Images
 * 
 * @apiParam {String} [student_id] 学号（模糊匹配）
 * @apiParam {String} [student_name] 姓名（模糊匹配）
 * @apiParam {String} [major] 专业（模糊匹配）
 * @apiParam {String} [year] 年级（精确匹配）
 * @apiParam {String} [college] 学院（精确匹配）
 * 
 * @apiSuccess {Object[]} data 查询结果
 * @apiSuccess {Number} data.id 记录ID
 * @apiSuccess {String} data.student_name 学生姓名
 * @apiSuccess {String} data.student_id 学号
 * @apiSuccess {String} data.year 年级
 * @apiSuccess {String} data.major 专业
 * @apiSuccess {String} data.college 学院
 * @apiSuccess {String} data.image_url 完整图片URL
 */
app.get('/api/images/search', async (req, res) => {
    try {
        const { student_id, student_name, major, year, college, image_type } = req.query;
        
        const connection = await mysql.createConnection(dbConfig);
        let query = 'SELECT * FROM images WHERE 1=1';
        const params = [];
        
        if (student_id) {
            query += ' AND student_id LIKE ?';
            params.push(`%${student_id}%`);
        }
        if (student_name) {
            query += ' AND student_name LIKE ?';
            params.push(`%${student_name}%`);
        }
        if (major) {
            query += ' AND major LIKE ?';
            params.push(`%${major}%`);
        }
        if (year) {
            query += ' AND year = ?';
            params.push(year);
        }
        if (college) {
            query += ' AND college = ?';
            params.push(college);
        }
        if (image_type) {
            query += ' AND image_type = ?';
            params.push(image_type);
        }
        
        const [rows] = await connection.execute(query, params);
        await connection.end();
        
        // 拼接完整URL
        const baseUrl = `${req.protocol}://${req.get('host')}`;
        const results = rows.map(row => ({
            ...row,
            image_url: `${baseUrl}/uploads/${path.basename(row.file_path)}`
        }));
        
        res.json({ success: true, data: results });
    } catch (error) {
        console.error('Search error:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
});

/**
 * @api {get} /api/images/student-style/meta 获取学生风采元数据
 * @apiName GetStudentStyleMeta
 * @apiGroup Images
 * 
 * @apiSuccess {Object} data 元数据
 * @apiSuccess {String[]} data.majors 所有专业列表
 * @apiSuccess {String[]} data.years 所有年级列表
 */
app.get('/api/images/student-style/meta', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        
        // 查询所有专业
        const [majors] = await connection.execute(
            'SELECT DISTINCT major FROM images WHERE image_type = ? ORDER BY major',
            ['学生风采']
        );
        
        // 查询所有年级
        const [years] = await connection.execute(
            'SELECT DISTINCT year FROM images WHERE image_type = ? ORDER BY year DESC',
            ['学生风采']
        );
        
        await connection.end();
        
        // 处理专业：去空格、去空值、再去重
        const processedMajors = [...new Set(
            majors
                .map(m => m.major ? m.major.trim() : '')
                .filter(major => major)
        )];
        
        // 处理年级：转为字符串、去空格、去空值、再去重
        const processedYears = [...new Set(
            years
                .map(y => y.year ? y.year.toString().trim() : '')
                .filter(year => year)
        )];
        
        res.json({ 
            success: true, 
            data: {
                majors: processedMajors,
                years: processedYears
            }
        });
    } catch (error) {
        console.error('Meta query error:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
});

/**
 * @api {get} /api/images/graduation/majors 获取毕业照所有专业
 * @apiName GetGraduationMajors
 * @apiGroup Graduation
 * 
 * @apiSuccess {Object} data
 * @apiSuccess {String[]} data.majors 所有专业列表(已去空格和去重)
 */
app.get('/api/images/graduation/majors', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        
        // 查询毕业照类型的所有专业
        const [majors] = await connection.execute(
            'SELECT major FROM images WHERE image_type = ?',
            ['毕业照']
        );
        
        await connection.end();
        
        // 严格处理：去空格 → 去空值 → 去重 → 排序
        const processedMajors = [...new Set(
            majors
                .map(m => m.major ? m.major.trim() : '')
                .filter(major => major)
        )].sort();
        
        res.json({ 
            success: true, 
            data: {
                majors: processedMajors
            }
        });
    } catch (error) {
        console.error('Graduation majors error:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
});

/**
 * @api {get} /api/images/graduation/years 获取指定专业的届数
 * @apiName GetGraduationYearsByMajor
 * @apiGroup Graduation
 * 
 * @apiParam {String} major 专业名称
 * 
 * @apiSuccess {Object} data
 * @apiSuccess {String[]} data.years 届数列表(已去空格和去重)
 */
app.get('/api/images/graduation/years', async (req, res) => {
    try {
        const { major } = req.query;
        if (!major) {
            return res.status(400).json({ error: 'Major parameter is required' });
        }
        
        const connection = await mysql.createConnection(dbConfig);
        
        // 查询指定专业的所有届数
        const [years] = await connection.execute(
            'SELECT year FROM images WHERE image_type = ? AND TRIM(major) = ?',
            ['毕业照', major.trim()]
        );
        
        await connection.end();
        
        // 严格处理：转为字符串 → 去空格 → 去空值 → 去重 → 倒序排序
        const processedYears = [...new Set(
            years
                .map(y => y.year ? y.year.toString().trim() : '')
                .filter(year => year)
        )].sort((a, b) => b.localeCompare(a)); // 倒序排序
        
        res.json({ 
            success: true, 
            data: {
                years: processedYears
            }
        });
    } catch (error) {
        console.error('Graduation years error:', error);
        res.status(500).json({ error: 'Internal server error' });
    }
});

/**
 * @api {get} /api/students/by-id 通过学号查询学生
 * @apiName GetStudentsById
 * @apiGroup Students
 * 
 * @apiParam {String} student_id 学号（支持模糊匹配）
 * 
 * @apiSuccess {Object[]} data 学生信息数组
 * @apiSuccess {String} data.student_id 学号
 * @apiSuccess {String} data.student_name 姓名
 * @apiSuccess {String} data.year 年级
 * @apiSuccess {String} data.major 专业
 * @apiSuccess {String} data.image_url 照片URL
 */
app.get('/api/students/by-id', async (req, res) => {
    try {
        const { student_id } = req.query;
        
        if (!student_id || student_id.trim() === '') {
            return res.status(400).json({ error: '学号不能为空' });
        }

        const connection = await mysql.createConnection(dbConfig);
        
        const [students] = await connection.execute(
            `SELECT 
                student_id,
                student_name,
                year,
                major,
                CONCAT(?, '/uploads/', SUBSTRING_INDEX(file_path, '/', -1)) AS image_url
             FROM images 
             WHERE student_id LIKE ? 
             AND student_id IS NOT NULL
             ORDER BY student_id`,
            [`${req.protocol}://${req.get('host')}`, `%${student_id.trim()}%`]
        );
        
        await connection.end();
        
        res.json({ 
            success: true, 
            data: students
        });
    } catch (error) {
        console.error('查询学号错误:', error);
        res.status(500).json({ error: '服务器内部错误' });
    }
});


/**
 * @api {get} /api/students/by-name 通过姓名查询学生
 * @apiName GetStudentsByName
 * @apiGroup Students
 * 
 * @apiParam {String} name 学生姓名（支持模糊匹配）
 * 
 * @apiSuccess {Object[]} data 学生信息数组
 * @apiSuccess {String} data.student_id 学号
 * @apiSuccess {String} data.student_name 姓名
 * @apiSuccess {String} data.year 年级
 * @apiSuccess {String} data.major 专业
 * @apiSuccess {String} data.image_url 照片URL
 */
app.get('/api/students/by-name', async (req, res) => {
    try {
        const { name } = req.query;
        
        if (!name || name.trim() === '') {
            return res.status(400).json({ error: '姓名不能为空' });
        }

        const connection = await mysql.createConnection(dbConfig);
        
        const [students] = await connection.execute(
            `SELECT 
                student_id,
                student_name,
                year,
                major,
                CONCAT(?, '/uploads/', SUBSTRING_INDEX(file_path, '/', -1)) AS image_url
             FROM images 
             WHERE student_name LIKE ? 
             AND student_name IS NOT NULL
             ORDER BY student_name`,
            [`${req.protocol}://${req.get('host')}`, `%${name.trim()}%`]
        );
        
        await connection.end();
        
        res.json({ 
            success: true, 
            data: students
        });
    } catch (error) {
        console.error('查询姓名错误:', error);
        res.status(500).json({ error: '服务器内部错误' });
    }
});

// 解析文件名
function parseFileName(filename) {
    // 移除扩展名
    const basename = path.basename(filename, path.extname(filename));
    
    // 定义两种分隔符模式：下划线和连字符
    const underscoreParts = basename.split('_');
    const hyphenParts = basename.split('-');
    
    // 辅助函数：清理班级信息，移除括号和数字
    function cleanClassInfo(classInfo) {
        if (!classInfo) return null;
        return classInfo.replace(/\s*\(\d+\)/, '').replace(/\s*（\d+）/, '');
    }
    
    // 第一种格式：2025届_智能互联网络技术_智能互联网络技术4班 (1)
    if (underscoreParts.length === 3) {
        return {
            studentName: null,
            studentId: null,
            year: underscoreParts[0],
            major: underscoreParts[1],
            classInfo: cleanClassInfo(underscoreParts[2])
        };
    } 
    // 第二种格式：曾泽佳_23173660_2026届_大数据技术_大数据技术6班
    else if (underscoreParts.length === 5) {
        return {
            studentName: underscoreParts[0],
            studentId: underscoreParts[1],
            year: underscoreParts[2],
            major: underscoreParts[3],
            classInfo: cleanClassInfo(underscoreParts[4])
        };
    }
    // 第三种格式：巴稚巽-22153501-2025届-视觉传达设计-视觉传达设计5班（1）
    else if (hyphenParts.length === 5) {
        return {
            studentName: hyphenParts[0],
            studentId: hyphenParts[1],
            year: hyphenParts[2],
            major: hyphenParts[3],
            classInfo: cleanClassInfo(hyphenParts[4])
        };
    }
    else {
        // 无法识别的格式
        return {
            studentName: null,
            studentId: null,
            year: null,
            major: null,
            classInfo: null
        };
    }
}

// 启动服务器
app.listen(port, () => {
    console.log(`Server running on http://localhost:${port}`);
});